# -*- coding: utf-8 -*-
# !/usr/bin/env python
'''
@Author: 赵天天
@LastEditors: 赵天天
@email: dayday_1996@163.com
@Descripttion: 安全报告接口文件
@Date: 2019-04-28 17:25:25
@LastEditTime: 2019-04-30 09:53:38
'''

from flask import Blueprint, g
from psycopg2 import connect
from psycopg2.extras import RealDictCursor
import json

from config import config
from lib import auth
from lib.validform import V, ValidateForm
app = Blueprint(__name__ + "_app", __name__)


# 连接/关闭 数据库
@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem_stc)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


# 攻击事件情况
@app.route('/api/report/drawtranBar', methods=['POST'])
@auth.permission("safetyReport")
def drawtranBar():
    try:
        form = ValidateForm(startTime=['开始时间', V.required()],
                            endTime=['结束时间', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        # 攻击事件情况
        g.cursor.execute("""
            select array_agg(value) as value,
                array_agg(unit) as key
            from (
                select coalesce(sum(attack_event_num),0) as value,
                t.unit
                from (select  agency_name as unit
                from (
                    select distinct a.* from sys_agency_info_fdw a,h_information_system_fdw b
            where a.agency_id = b.agency_id
            and b.state = '1' and a.state = '1'
                ) as n
                )  t
                left join  t_attack_event_info as b
                on t.unit = b.unit and static_date > (select date '%s' - interval '1 day' as start_time) and  
                    static_date < (select date '%s' + interval '1 day' as end_time)
                group by t.unit
            ) as a
        """ % (data['startTime'], data['endTime']))
        attackevent = g.cursor.fetchone()
        totalattackevent = 0
        for item in attackevent['value']:
            totalattackevent = totalattackevent + item
        
        # 深度载荷分析
        g.cursor.execute("""
            select array_agg(value1) as total,
                array_agg(value2) as feedback,
                array_agg(unit) as key,
                sum(value1) ::integer as total1,
                sum(value2) ::integer as feedback1
            from (
                select coalesce(sum(task_num),0) as value1,
                    coalesce(sum(feedback_num),0) as value2,
                t.unit
                from (select  agency_name as unit,
                        agency_id as unit_id
                from (
                    select distinct a.* from sys_agency_info_fdw a,h_information_system_fdw b
            where a.agency_id = b.agency_id
            and b.state = '1' and a.state = '1'
                ) as n ) as t
                left join  t_task_statistics as b
                on t.unit_id = b.unit_id and static_date > (select date '%s' - interval '1 day' as start_time) and  
                static_date < (select date '%s' + interval '1 day' as end_time)
                and task_type = '深度分析任务'
                group by t.unit
                order by value1 desc
            ) as a
        """ % (data['startTime'], data['endTime']))
        loadDepthAnalysis = g.cursor.fetchone()
        
        # 流量监测
        g.cursor.execute("""
        select array_agg(value1) as total,
               array_agg(value2) as feedback,
               array_agg(unit) as key,
               sum(value1) ::integer as total1,
               sum(value2) ::integer as feedback1
        from (
            select coalesce(sum(task_num),0) as value1,
                coalesce(sum(feedback_num),0) as value2,
            t.unit
            from (select  agency_name as unit,
                    agency_id as unit_id
            from (
                    select distinct a.* from sys_agency_info_fdw a,h_information_system_fdw b
                        where a.agency_id = b.agency_id
                        and b.state = '1' and a.state = '1'
                            ) as n ) as t
            left join  t_task_statistics as b
            on t.unit_id = b.unit_id and static_date > (select date '%s' - interval '1 day' as start_time) and  
            static_date < (select date '%s' + interval '1 day' as end_time)
            and task_type = '流量监测任务'
            group by t.unit
            order by value1 desc
        ) as a
        """ % (data['startTime'], data['endTime']))
        trafficMonitoring = g.cursor.fetchone()
        
        # 审计监测
        g.cursor.execute("""
        select array_agg(value1) as total,
               array_agg(value2) as feedback,
               array_agg(unit) as key,
               sum(value1) ::integer as total1,
               sum(value2) ::integer as feedback1
        from (
            select coalesce(sum(task_num),0) as value1,
                coalesce(sum(feedback_num),0) as value2,
            t.unit
            from (select  agency_name as unit,
                    agency_id as unit_id
            from (
                    select distinct a.* from sys_agency_info_fdw a,h_information_system_fdw b
                    where a.agency_id = b.agency_id
                    and b.state = '1' and a.state = '1'
                        ) as n ) as t
            left join  t_task_statistics as b
            on t.unit_id = b.unit_id and static_date > (select date '%s' - interval '1 day' as start_time) and  
            static_date < (select date '%s' + interval '1 day' as end_time)
            and task_type = '审计监测任务'
            group by t.unit
            order by value1 desc
        ) as a
        """ % (data['startTime'], data['endTime']))
        auditMonitoring = g.cursor.fetchone()
        
        # 脆弱性监测
        g.cursor.execute("""
        select array_agg(value1) as total,
               array_agg(value2) as feedback,
               array_agg(unit) as key,
               sum(value1) ::integer as total1,
               sum(value2) ::integer as feedback1
        from (
            select coalesce(sum(task_num),0) as value1,
                coalesce(sum(feedback_num),0) as value2,
            t.unit
            from (select  agency_name as unit,
                    agency_id as unit_id
            from (
                    select distinct a.* from sys_agency_info_fdw a,h_information_system_fdw b
                    where a.agency_id = b.agency_id
                    and b.state = '1' and a.state = '1'
                        ) as n ) as t
            left join  t_task_statistics as b
            on t.unit_id = b.unit_id and static_date > (select date '%s' - interval '1 day' as start_time) and  
            static_date < (select date '%s' + interval '1 day' as end_time)
            and task_type = '脆弱性监测任务'
            group by t.unit
            order by value1 desc
        ) as a
        """ % (data['startTime'], data['endTime']))
        vulnerabilityMonitoring = g.cursor.fetchone()
        return json.dumps({
            "status": "success",
            "data": {
                "attackevent": {
                    'data': attackevent,
                    'color': '#1f92ef',
                    'total': totalattackevent
                },
                "loadDepthAnalysis": {
                    'data': loadDepthAnalysis,
                    'color': ['#1F92EF', '#52cb7b']
                },
                "trafficMonitoring": {
                    'data': trafficMonitoring,
                    'color': ['#1F92EF', '#52cb7b']
                },
                "auditMonitoring": {
                    'data': auditMonitoring,
                    'color': ['#1F92EF', '#52cb7b']
                },
                "vulnerabilityMonitoring": {
                    'data': vulnerabilityMonitoring,
                    'color': ['#1F92EF', '#52cb7b']
                }
            }
        })
    except Exception as e:
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': str(e)})


# 风险事项等级
@app.route('/api/report/drawpie', methods=['POST'])
@auth.permission("safetyReport")
def drawpie():
    try:
        form = ValidateForm(startTime=['开始时间', V.required()],
                            endTime=['结束时间', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        # 风险预警情况 
        g.cursor.execute("""
        select coalesce(sum(already_warn_num),0) as value,
            '已预警' as name
        from t_riskitem_warning_info
        where static_date > (select date '%s' - interval '1 day' as start_time) and  
                    static_date < (select date '%s' + interval '1 day' as end_time)
        union all 
        select coalesce(sum(not_warn_num),0) as value,
            '未预警' as key
        from t_riskitem_warning_info
        where static_date > (select date '%s' - interval '1 day' as start_time) and  
                    static_date < (select date '%s' + interval '1 day' as end_time)
        """ % (data['startTime'], data['endTime'],
               data['startTime'], data['endTime']))
        itemWarning = g.cursor.fetchall()

        total = 0
        for item in itemWarning:
            total = item['value'] + total 
        return json.dumps({
            "status": "success",
            "data": {
                "itemWarning": itemWarning,
                "color": ['#52CB7B', '#FFD140'],
                'totalitemWarning': total
            }
        })
    except Exception:
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': '查询失败'})


# 风险预警情况
@app.route('/api/report/drawBar', methods=['POST'])
@auth.permission("safetyReport")
def drawBar():
    try:
        form = ValidateForm(startTime=['开始时间', V.required()],
                            endTime=['结束时间', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        # 风险事项等级
        g.cursor.execute("""
        select array_agg(value) as value,
            array_agg(grade) as key
        from (
            select coalesce(sum(riskitem_num),0) as value,
                case t.grade when '1' then '一般' 
                             when '2' then '较大'
                             when '3' then '重大' 
                             when '4' then '特大' end as grade
            from (values('1'),('2') ,('3'), ('4')) as t(grade) 
            left join  t_riskitemgrade_statistics as b
            on t.grade = b.grade  and static_time > (select date '%s' + interval '1 day' as start_time) 
                and  static_time < (select date '%s' + interval '1 day' as end_time)
            group by t.grade
            order by t.grade
        ) as a
        """ % (data['startTime'], data['endTime']))
        itemlevel = g.cursor.fetchone()
        totalitemlevel = 0
        for item in itemlevel['value']: 
            totalitemlevel = totalitemlevel + item
        # 风险事项类型
        g.cursor.execute("""
        select array_agg(value) as value,
            array_agg(riskitem_type) as key
        from (
            select coalesce(sum(riskitem_num),0) as value,
                t.riskitem_type,
                t.type_index
            from (values('新兴','1'),('流行','2') ,('高危','3'), ('攻击','4'),('其他','5')) as t(riskitem_type, type_index) 
            left join  t_riskitem_type_statistics as b
            on t.riskitem_type = b.riskitem_type and static_date > (select date '%s' - interval '1 day' as start_time) and  
                    static_date < (select date '%s' + interval '1 day' as end_time)
            group by t.riskitem_type,
                     t.type_index
            order by t.type_index
        ) as a
        """ % (data['startTime'], data['endTime']))
        itemgrade = g.cursor.fetchone()
        totalitemgrade = 0
        for item in itemgrade['value']: 
            totalitemgrade = totalitemgrade + item
        return json.dumps({
            "status": "success",
            "data": {
                "itemlevel": {
                    'data': itemlevel,
                    'color': ['#FFD140', '#FEA348', '#FE7547', '#FF4749'],
                    'total': totalitemlevel
                },
                "itemgrade": {
                    'data': itemgrade,
                    'color': ['#1f92ef', '#1f92ef', '#1f92ef', '#1f92ef', '#1f92ef'],
                    'total': totalitemgrade
                }
            }
        })
    except Exception as e:
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': str(e)})


# 统计日志
@app.route('/api/report/staticLog', methods=['POST'])
@auth.permission("safetyReport")
def staticLog():
    try:
        form = ValidateForm(startTime=['开始时间', V.required()],
                            endTime=['结束时间', V.required()])
        (flag, data) = form.validate()
        if not flag:
            data['status'] = 'fail'
            return json.dumps(data)
        data1 = {
                    "query": {
                        "bool": {"filter": [{
                            'range': {
                                'ts': {'gte': data['startTime'] + ' 00:00:00', 'lte': data['endTime'] + ' 00:00:00'}}}]}}, 
                                    'aggs': {"groupData": {"terms": {"field": "stype", "size": 20}}}}
        esData = g.es.search(index='AIR', body=data1)
        key = []
        value = []
        total = 0
        for item in esData['aggregations']['groupData']['buckets']:
            key.append(item['key'])
            value.append(item['doc_count'])
            total = total + item['doc_count']
        return json.dumps({
            "status": "success",
            'data': {
                'data': {
                    'key': key,
                    'value': value
                },
                'total': total
            }
        })
    except Exception as e:
        return json.dumps({"status": "except", 'msg': '查询失败', 'warn': str(e)})

